from openpyxl import load_workbook

from config.Varconfig import testdataPath


class ExcelOperate:

    def __init__(self):
        self.workbook = None
        # self.sheetname = None

    def load_workbook(self,filename):
        try:
            self.workbook = load_workbook(filename)
        except Exception as e:
            print(e)

    def get_sheet_by_name(self,sheetname):
        try:
            # self.sheet = self.workbook[sheetname]
            return self.workbook[sheetname]
        except Exception as e:
            print(e)

    def get_row_nums(self,sheet):
        try:
            # return self.sheet.max_row
            return sheet.max_row
        except Exception as e:
            print(e)

    def get_col_nums(self,sheet):
        try:
            # return self.sheet.max_column
            return sheet.max_column
        except Exception as e:
            print(e)

    def get_row_values(self,row,sheet):

        try:
            columns = sheet.max_column
            row_data = []
            for i in range(1,columns+1):
                cell_values = sheet.cell(row=row,column=i).value
                row_data.append(cell_values)
            return row_data
        except Exception as e:
            print(e)

    def get_cell_values(self,row,column,sheet):
        try:
            return sheet.cell(row=row,column=column).value
        except Exception as e:
            print(e)

    def write_value(self,row,column,value,fileName,sheet):
        try:
            sheet.cell(row=row,column=column,value=value)
            self.workbook.save(fileName)
        except Exception as e:
            print(e)
if __name__ == '__main__':
    excel = ExcelOperate()
    excel.load_workbook(testdataPath)
    sheet = excel.get_sheet_by_name('loginandsendmail')
    print(excel.get_col_nums(sheet))
    print(excel.get_row_nums(sheet))
    print(excel.get_row_values(2,sheet))
    print(excel.get_cell_values(2,2,sheet))
    print(excel.write_value(2,8,'pass',testdataPath,sheet))




